package menuTree;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.primefaces.model.TreeNode;

import common.ConnectionDAO;


import AllegroWebApi.CatInfoType;
import AllegroWebApi.holders.ArrayOfCatsHolder;

public class MenuTreeDAO {
	Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    PreparedStatement ps;

    public MenuTreeDAO(){
		 con = ConnectionDAO.getConnection();
	}
    
    
    public ArrayList<TreeNodeImpl> getCatsByParent( TreeNodeImpl p){
    	try{
    		//System.out.println("id : " + p.getId() );
    		 ArrayList<TreeNodeImpl> list =  new ArrayList<TreeNodeImpl>();
	    	  /*   ps  = con.prepareStatement("SELECT c.id, c.parent_id, c.name, count(sb.id)  child_count " +
	    	     		"FROM category c  left join category sb on c.id = sb.parent_id " +
	    	     		"WHERE c.parent_id=? and c.id!=0 " +
	    	     		"group by 1 , 2 , 3 order by name");*/
    		 
    		 	ps = con.prepareStatement("SELECT c.id, c.parent_id, c.name, "+
										" count(sb.id)  child_count, "+
										"ofc.count offer_count "+
										 "FROM category c  left join category sb on c.id = sb.parent_id  "+
										"left join  ( select o.category_id, count(o.id) count from offer o  "+
										"where o.category_id in  "+
										"(select id from category where parent_id =? ) "+
										" group by 1) "+
										 "ofc on ofc.category_id = c.id "+
										"WHERE c.parent_id=? and c.id!=0 "+
										"group by 1 , 2 , 3 order by name ");

	    	    
	    	    ps.setInt(1, p.getId());
	    	    ps.setInt(2, p.getId());
	    	    try{
	    	    rs = ps.executeQuery();
	    	    }catch(Exception t){
	    	    	return null;
	    	    }
	    	    TreeNodeImpl data ;
	        while(rs.next()){
	        	//System.out.println("id : " +rs.getInt("id") + " name:"+ rs.getString("name"));
	        	data = new TreeNodeImpl(rs.getInt("id") , rs.getInt("parent_id"), rs.getString("name") + " ("+ rs.getInt("child_count")+" , "+ rs.getString("offer_count") +")", p);		        	
	        	list.add(data);
	        }
	        return list;
	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);
	        } catch (Exception ex) {
	            Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);
	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		return null;
	}
   
    
    
    public void addCategory(ArrayOfCatsHolder list){
    	 try {
	           PreparedStatement pr = con.prepareStatement("insert into category(id , parent_id, name) " +	"values( ? , ? , ?);");
	           
	           for(CatInfoType data : list.value){
	        	   pr.setInt(1, data.getCatId());
	        	   pr.setInt(2, data.getCatParent());
	        	   pr.setString(3, data.getCatName());
	        	   
	        	   if(pr.executeUpdate()>0)
	        	   System.out.println("added : " + data.getCatName());
	           }
	         

	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
    }


	public void deleteNodes(TreeNode[] selectedNodes) {
		 try {

			 con.setAutoCommit(false);
			 PreparedStatement atrArray;
			 PreparedStatement pr1;
			 PreparedStatement pr2;
			 PreparedStatement pr3;
			 PreparedStatement pr4;
			 PreparedStatement pr5;
			 pr1 = con.prepareStatement( "delete from sell_attribute_value " +
					 " where sell_attribute_id  = ?;");
			 pr2 = con.prepareStatement( "delete from sell_attribute " +
					 " where id in ( " +
					 "select sell_attribute_id from category_sell_attribute where category_id = ?);");
			 pr3 = con.prepareStatement("delete from category_sell_attribute where category_id = ?;");
			 pr4 = con.prepareStatement("delete from offer where category_id = ?;");
			 pr5 = con.prepareStatement("delete from category where id = ?;");
			 
	          for(int i = 0 ; i < selectedNodes.length ; i++){
	        	  TreeNode data = selectedNodes[i];
	        	  if(data.getChildCount()==0){
	        		  
	        		  atrArray = con.prepareStatement("select sell_attribute_id from category_sell_attribute where category_id = ?;");
	        		 atrArray.setInt(1, ((TreeNodeImpl)data).getId());
	        		 ResultSet rs = atrArray.executeQuery();
	        		 Integer idAtr = null;
	        		  while(rs.next() ){
	        			  idAtr =  rs.getInt("sell_attribute_id");
	        			 pr1.setInt(1, idAtr);
		        		  pr1.addBatch();
	        		  }
	        					
	        		  pr1.executeBatch();
		        		  
	        		  pr2.setInt(1, ((TreeNodeImpl)data).getId());
	        		  pr2.addBatch();
	        		  
	        		  pr3.setInt(1, ((TreeNodeImpl)data).getId());
	        		  pr3.addBatch();
	        		  
	        		  pr4.setInt(1, ((TreeNodeImpl)data).getId());
	        		  pr4.addBatch();
	        		  
	        		  pr5.setInt(1, ((TreeNodeImpl)data).getId());
	        		 pr5.addBatch();
	        		  System.out.println("add cat to delete : " + ((TreeNodeImpl)data).getId() + " count:"+ i);
	        	  }
	          }
          			
          			pr2.executeBatch();
        		  System.out.println("delted atr");
        		  pr3.executeBatch();
        		  System.out.println("delted cat sel atr");
        		  pr4.executeBatch();
      			 System.out.println("del offer");
        		  pr5.executeBatch();
    			 System.out.println("del cat");
	          
	         
			 con.commit();
	        } catch (SQLException ex) {
	        	try {
					con.rollback();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	            Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } catch(Exception e){
	        	try {
					con.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
	        }
		 	finally {
		 		try {
					con.setAutoCommit(true);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		
		
	}


	public void moveCatToParentAndDelete(TreeNode[] selectedNodes) {
		 try {
			 con.setAutoCommit(false);
			 TreeNodeImpl node;
			 HashMap<String, Integer> parentParam = new HashMap<String, Integer>();
			 HashMap<String, Integer> childParam = new HashMap<String, Integer>();
			 
			 String getParamSql = "select csa.id , title from sell_attribute sa "+
									" join category_sell_attribute csa on  sa.id = csa.sell_attribute_id "+
									" where csa.category_id = ?; ";
			 PreparedStatement paramPs = con.prepareStatement(getParamSql);
			 PreparedStatement updateParam = con.prepareStatement(" update sell_attribute_value "+
						" set sell_attribute_id = ? "+
						" where  "+
						" sell_attribute_id in (  "+
						" 	select sell_attribute_id  "+
						" 	from category_sell_attribute "+
						 " 	where category_id = ?" +
						 "	and sell_attribute_id = ?);");
			 PreparedStatement updateOffer = con.prepareStatement("update offer set category_id = ?" +
			 		" where category_id = ?;");
			 
			 PreparedStatement updateCatParam = con.prepareStatement("update category_sell_attribute " +
			 		"set category_id = ? " +
			 		"where sell_attribute_id = ? ;");
			 PreparedStatement deleteParam = con.prepareStatement("delete from sell_attribute" +
			 		" where id = ?;");
			 for(int i = 0 ; i < selectedNodes.length ; i ++){
				 parentParam = new HashMap<String, Integer>();
				 childParam = new HashMap<String, Integer>();
				 
				 node = (TreeNodeImpl)selectedNodes[i];
				 paramPs.setInt(1, node.getId());
				 ResultSet rs = paramPs.executeQuery();
				 while(rs.next()){
					 childParam.put(rs.getString("title").trim(), rs.getInt("id"));
				 }
				 
				 paramPs.setInt(1, ((TreeNodeImpl)node.getParent()).getId());
				 rs = paramPs.executeQuery();
				 while(rs.next()){
					 parentParam.put(rs.getString("title").trim(), rs.getInt("id"));
				 }
				 
				 // przepięcie parametrów dziecka do rodzica
				 Set<String> param = childParam.keySet();
				 Iterator<String> paramIter = param.iterator();
				 String paramName=null;
				 Integer idParam;
				 while(paramIter.hasNext()){ // przepięcie parametrów
					 paramName = paramIter.next();
					 
					 if(parentParam.containsKey(paramName)){ // rodzic posiada taki parametr
						 // przepiąć i usunąć
						idParam = parentParam.get(paramName);
						
						// dla każdego produktu/ dla jednego parametru
						updateParam.setInt(1, idParam);
						updateParam.setInt(2, node.getId());
						updateParam.setString(3, paramName);
						System.out.println("update child param id to parent id");
						updateParam.executeUpdate();
						// i usunąć param dziecka
						
						deleteParam.setInt(1, childParam.get(paramName));
						deleteParam.executeUpdate();
						
						 
					 }else{ // rodzic nie posiada parametru
						 System.out.println("rodzic nie posiada param." + paramName);
						 // przepiąć parametr dziecka do rodzica 
						 updateCatParam.setInt(1, ((TreeNodeImpl) node.getParent()).getId());
						 updateCatParam.setInt(2, childParam.get(paramName));
						 updateCatParam.executeUpdate();
						 idParam = childParam.get(paramName);
						 
						// dla każdego produktu/ dla jednego parametru
							updateParam.setInt(1, idParam);
							updateParam.setInt(2, node.getId());
							updateParam.setString(3, paramName);
							System.out.println("update child param id to parent id");
							updateParam.executeUpdate();
						 
					 }
					 
				 }
				 updateOffer.setInt(1, ((TreeNodeImpl) node.getParent()).getId());
				 updateOffer.setInt(2, node.getId());
				 System.out.println(" move offer child to offer parent. From :" +node.getId() + " to : " + ((TreeNodeImpl) node.getParent()).getId() );
				 updateOffer.executeUpdate();
				 
				 
				 
			 }
	         
			 //con.rollback();
			 con.commit();
	        } catch (SQLException ex) {
	        	try {
					con.rollback();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	            Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } catch(Exception e){
	        	try {
					con.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
	        }
		 	finally {
		 		try {
					con.setAutoCommit(true);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(MenuTreeDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
	}
}
